package com.lxu.service;

import com.lxu.entity.StageFieldPushConfigEntity;
import com.lxu.entity.StagePushConfigEntity;
import com.lxu.entity.StageTablePushConfigEntity;
import com.lxu.utils.SystemUtil;
import org.apache.commons.lang3.ArrayUtils;
import org.junit.Assert;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 项目阶段之间数据推送
 */
@Service
public class ProjectStageDataPushService {

    @Resource(name = "ds3JdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 数据推送
     *
     * @param conditions
     * @return
     */
    @Transactional
    public boolean push(Map<String, String> conditions) {

        Assert.assertNotNull(conditions.get("fromStage"));
        Assert.assertNotNull(conditions.get("toStage"));

        StagePushConfigEntity entity = getPushSqlByStage(conditions.get("fromStage"), conditions.get("toStage"));

        /**
         * 獲取待推送的表
         */
        List<StageTablePushConfigEntity> tables = getAllNeedPushTable(entity.getId());

        /**
         * 獲取目标配置表信息
         */
        Map<String, String> tableRelation = new HashMap<>();
        for (StageTablePushConfigEntity table : tables) {
            List<StageFieldPushConfigEntity> fields = getAllFieldsByTable(table.getId());
            table.setFields(fields);
        }

        /**
         * 組裝源和目標之間的關係
         */
        Map<String, String> relation = assembleSourceAndDestTableRelation(tables,tableRelation);

        /**
         * 组装目标内部的关系
         */
        List<Map<String, String>> tableInnerRelation = assembleDestTableInnerRelation(tables);
        /**
         * 执行源表SQL，获取源表数据
         */
        List<Map<String, Object>> sourceData = jdbcTemplate.queryForList(SystemUtil.fillDynamicParameters(entity.getPushSql(), conditions));
        /**
         * 填充目标表ID为源表ID，主要是处理目标表主子表主键关系问题
         */
        fillDestTableIds(sourceData,relation,tableRelation);
        /**
         * 形成目标表的数据
         */
        List<Map<String, Object>> resultData = generateDestTableData(sourceData,relation,tableInnerRelation);
        /**
         * 组装目标表字段和值的对应关系
         */
        List<Map<String, Map<String, String>>> insertSqlList = assembleSqlMap(resultData);
        /**
         * 移除非法SQL
         */
        removeIllegalSql(insertSqlList);
        /**
         * 执行目标表入库SQL
         */
        executeSql(assembleFinalSql(insertSqlList));
        return true;
    }

    /**
     * 填充目标表的ID
     * @param sourceData 源表数据
     * @param relation  源表和目标表之间的关系
     * @param tableRelation 目标表信息
     */
    private void fillDestTableIds(List<Map<String, Object>> sourceData,Map<String, String> relation,Map<String, String> tableRelation) {
        for (Map<String, Object> source : sourceData) {
            tableRelation.forEach((key, value) -> {
                source.put(value + "->id", source.get(key + "->id"));
                relation.put(value + "->id", value + "->id");
            });
        }
    }

    /**
     * 组装目标表内部关系
     * @param tables
     * @return
     */
    private List<Map<String, String>> assembleDestTableInnerRelation(List<StageTablePushConfigEntity> tables) {
        List<Map<String, String>> tableInnerRelation = new ArrayList<>();
        for (StageTablePushConfigEntity table : tables) {
            if (StringUtils.isEmpty(table.getFieldRelation())) {
                Map<String, String> map = new HashMap<>();
                map.put(table.getDestTableEn() + "->id", null);
                tableInnerRelation.add(map);
            } else {
                String[] dependenceFields = table.getFieldRelation().split(",");
                if (ArrayUtils.isEmpty(dependenceFields)) {
                    continue;
                }
                Map<String, String> map = new HashMap<>();
                for (String dependenceField : dependenceFields) {
                    String dependenceAttr = table.getDestTableEn() + "->" + dependenceField.split(":")[0];
                    String dependenceOnAttr = table.getTableRelation() + "->" + dependenceField.split(":")[1];
                    map.put(dependenceOnAttr, dependenceAttr);
                    tableInnerRelation.add(map);
                }
            }
        }
        return tableInnerRelation;
    }


    /**
     * 组装源表和目标表的关系
     * @param tables 待推送的表
     * @param tableRelation 目标表信息
     * @return
     */
    private Map<String, String> assembleSourceAndDestTableRelation(List<StageTablePushConfigEntity> tables,Map<String, String> tableRelation){
        Map<String, String> relation = new HashMap<>();
        for (StageTablePushConfigEntity table : tables) {
            tableRelation.put(table.getFields().get(0).getSourceTable(), table.getDestTableEn());
            String destTableName = table.getDestTableEn();
            List<StageFieldPushConfigEntity> fields = table.getFields();
            for (StageFieldPushConfigEntity field : fields) {
                String source = field.getSourceTable() + "->" + field.getSourceField();
                String destination = destTableName + "->" + field.getDestField();
                relation.put(source, destination);
            }
        }
        return relation;
    }

    /**
     * 形成目标表数据
     * @param sourceData 源表数据
     * @param relation  源表和目标表之间的关系
     * @param tableInnerRelation 目标表内部关系
     * @return
     */
    private List<Map<String, Object>> generateDestTableData(List<Map<String, Object>> sourceData,Map<String, String> relation,List<Map<String, String>> tableInnerRelation){
        List<Map<String, Object>> resultData = new ArrayList<>();
        for (Map<String, Object> data : sourceData) {
            Map<String, Object> resultDataMap = new HashMap<>();
            data.forEach((key, value) -> {
                relation.forEach((key1, value1) -> {
                    if (key.equals(key1)) {
                        resultDataMap.put(value1, String.valueOf(value));
                    }
                });
            });
            resultData.add(resultDataMap);
        }

        for (Map<String, Object> data : resultData) {
            tableInnerRelation.forEach((item) -> {
                item.forEach((key, value) -> {
                    if (value != null) {
                        data.put(value, data.get(key));
                    }
                });
            });
        }
        return resultData;
    }

    /**
     * 组装目标SQL所需的key和value，即insert语句需要的值
     *
     * @param resultData
     * @return
     */
    private List<Map<String, Map<String, String>>> assembleSqlMap(List<Map<String, Object>> resultData) {
        List<Map<String, Map<String, String>>> insertSqlList = new ArrayList<>();
        for (Map<String, Object> data : resultData) {
            Map<String, Map<String, String>> insertSqlMap = new HashMap<>();
            data.forEach((key, value) -> {
                String tableName = key.split("->")[0];
                String fieldName = key.split("->")[1];
                if (insertSqlMap.get(tableName) == null) {
                    Map<String, String> map = new HashMap<>();
                    map.put(fieldName, String.valueOf(value));
                    insertSqlMap.put(tableName, map);
                } else {
                    insertSqlMap.get(tableName).put(fieldName, String.valueOf(value));
                }
            });
            insertSqlList.add(insertSqlMap);
        }
        return insertSqlList;
    }


    @Transactional
    public void executeSql(Set<String> sqls) {
        sqls.forEach(item -> {
            jdbcTemplate.execute(item);
        });
    }

    /**
     * 组装最后的入库SQL
     *
     * @param insertSqlList
     * @return
     */
    private Set<String> assembleFinalSql(List<Map<String, Map<String, String>>> insertSqlList) {
        Set<String> sqls = new HashSet<>();
        for (Map<String, Map<String, String>> insertSql : insertSqlList) {
            insertSql.forEach((key, value) -> {
                StringBuffer sql = new StringBuffer();
                StringBuffer keys = new StringBuffer();
                StringBuffer values = new StringBuffer();
                sql.append("insert into ").append(key).append(" (");
                value.forEach((key1, value1) -> {
                    if (!(StringUtils.isEmpty(value1) || "null".equals(value1.toLowerCase()))) {
                        keys.append(key1).append(",");
                        values.append("'").append(value1).append("'").append(",");
                    }
                });
                keys.delete(keys.length() - 1, keys.length());
                values.delete(values.length() - 1, values.length());
                sql.append(keys.toString()).append(")").append("values(").append(values.toString()).append(")");
                sqls.add(sql.toString());
            });
        }
        return sqls;
    }

    /**
     * 移除不合法的SQL 如果id主键为空，SQL判定为不合法
     *
     * @param insertSqlList
     */
    private void removeIllegalSql(List<Map<String, Map<String, String>>> insertSqlList) {
        for (Map<String, Map<String, String>> insertSql : insertSqlList) {
            Iterator<Map.Entry<String, Map<String, String>>> iter = insertSql.entrySet().iterator();
            while (iter.hasNext()) {
                Map.Entry<String, Map<String, String>> item = iter.next();
                Map<String, String> values = item.getValue();
                String idValue = values.get("id");
                if (StringUtils.isEmpty(idValue) || "null".equals(idValue)) {
                    iter.remove();
                }
            }
        }
    }


    /**
     * 通过推送表获取该表需要推送的字段
     *
     * @param tableId
     * @return
     */
    private List<StageFieldPushConfigEntity> getAllFieldsByTable(String tableId) {
        String sql = "SELECT t.source_table,t.source_field,t.dest_field,t.default_value FROM yw_stage_field_push_config t WHERE t.table_id=?";
        return jdbcTemplate.query(sql, new Object[]{tableId}, new RowMapper<StageFieldPushConfigEntity>() {
            @Override
            public StageFieldPushConfigEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                StageFieldPushConfigEntity entity = new StageFieldPushConfigEntity();
                entity.setSourceTable(rs.getString(1));
                entity.setSourceField(rs.getString(2));
                entity.setDestField(rs.getString(3));
                entity.setDefaultValue(rs.getString(4));
                return entity;
            }
        });
    }

    /**
     * 获取該阶段需要推动的表
     *
     * @param configId 项目阶段数据推送表编号
     * @return
     */
    private List<StageTablePushConfigEntity> getAllNeedPushTable(String configId) {
        String sql = "SELECT t2.id, t2.dest_table_en,t2.table_relation,t2.field_relation FROM yw_stage_push_config t1,yw_stage_table_push_config t2"
                + " WHERE t1.id=t2.config_id AND t1.id=?";
        return jdbcTemplate.query(sql, new Object[]{configId}, new RowMapper<StageTablePushConfigEntity>() {
            @Override
            public StageTablePushConfigEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                StageTablePushConfigEntity entity = new StageTablePushConfigEntity();
                entity.setId(rs.getString(1));
                entity.setDestTableEn(rs.getString(2));
                entity.setTableRelation(rs.getString(3));
                entity.setFieldRelation(rs.getString(4));
                return entity;
            }
        });
    }

    /**
     * 获取該阶段需要推动的表
     *
     * @param fromStage 原始阶段
     * @param toStage   目标阶段
     * @return
     */
    private StagePushConfigEntity getPushSqlByStage(String fromStage, String toStage) {
        String sql = "SELECT t.id,t.from_stage,t.to_stage,t.push_sql FROM yw_stage_push_config t WHERE t.from_stage=? AND t.to_stage=?";
        List<StagePushConfigEntity> list = jdbcTemplate.query(sql, new Object[]{fromStage, toStage}, new RowMapper<StagePushConfigEntity>() {
            @Override
            public StagePushConfigEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                StagePushConfigEntity entity = new StagePushConfigEntity();
                entity.setId(rs.getString(1));
                entity.setFromStage(rs.getString(2));
                entity.setToStage(rs.getString(3));
                entity.setPushSql(rs.getString(4));
                return entity;
            }
        });
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }
}